Cannot create Availability Group listener

Introduction

In this lab, you will try to create an Availability Group listener. The Listener creation will fail. The goal is to try and identify the cause of the failure and fix the issue.

Objectives

At the end of this lab, you will be able to:

  • Understand the causes behind the AG listener creation issue.
  • Fix the issue and create a new Availability Group.

Estimated Time

30 minutes

Logon Information

Use the following credentials to login into virtual environment

  • Username: corpnet\cluadmin
  • Password: Pa$$w0rd

Before starting the training module, we recommend that you launch the labs and give them some time to stabilize. Please be aware that sometimes the AG may be in a resolving state and AG Replicas may be in a disconnected state. This is a platform issue and should stabilize after a few minutes

Environment review

Before you begin with the first exercise in the lab, let's review the lab environment.

  • In the lab, you have one Domain Controller and 3 nodes + 1 client computer.

  • AlwaysOnN1 and AlwaysOnN2 nodes are in the primary Datacenter.

  • AlwaysOnN3 is in the secondary datacenter.

  • For this lab, both the datacenters are in the same subnet.

  • Each node has Windows Server 2022 O/S installed.

  • SQL Server 2022 Standalone instances are installed on all the 3 nodes (i.e. AlwaysOnN1, AlwaysOnN2, AlwaysOnN3).

Back to the list of Labs

Exercise 1: Create a Listener for the Availability Group

In this exercise, you will learn how to Create a Listener for the Availability Group.

Tasks

  1. Create listener using the New Availability Group wizard.

    In this task, you will use the SQL Server management studio to create listener. Perform this task on the virtual machine AlwaysOnClient.

  2. Open SSMS and Connect to AlwaysOnN1, AlwaysOnN2 and AlwaysOnN3 SQL Instances. Availability Group (AGCorp) has already been deployed and the above 3 instances are the replicas. Identify the replica that is the primary replica and perform the next step on that instance.

  3. On the primary instance, expand Availability Groups - Expand AGCorp Availability Group - right click Availability Group Listener and click on the Add Listener option.

  4. In the New Availability Group Listener dialog, type the Listener DNS Name as AGCorpListen. Type the Port as 1433 and select Static IP for Network Mode and click Add.

  5. In the Add IP Address dialog, select the 10.1.1.0/24 subnet and type 10.1.1.106 as shown below. Click OK.

  6. Review the page and click Ok.

  7. After a few minutes, a pop-up message will show up. Review the message and click Ok. The below message might or might not show up in all listener creation failure scenarios.

  8. Click ok on the listener creation page to try and create the listener again.

  9. This time you will get another pop-up, review the message and click Ok. The below message (Error Number: 19471) is the common message that will show up in listener creation failure scenarios.

  10. Click cancel on the listener creation page.

  11. Feel free to recreate the listener as you try various options to fix the issue.

Congratulations!

You have successfully completed this exercise. Click Next to advance to the next exercise.

Exercise 2: Identify the cause of the failure and fix the issue

In this exercise, you will learn how to Identify the cause of the failure and fix the issue.

Tasks

  1. Find the root cause using the guidelines and cheat sheet provided below.

Guidelines

  1. This is a non-guided activity and the attendees are expected to try and troubleshoot this issue on their own.

  2. You can use any resources (including the internet or your own scripts), to troubleshoot the issue.

  3. You can use the tools discussed in the first module to help troubleshoot the issue.

  4. The possible causes discussed earlier in the lesson can be used as guidance for troubleshooting.

  5. The instructor will discuss the troubleshooting steps, cause and solution in detail after this lab session.

  6. You might have to login directly on the individual nodes to troubleshoot the issue.

  7. Ask yourself the below questions:

    • Where do you start?
    • What logs will provide me additional insights?
    • What tools can I use to troubleshoot this issue?
    • Could one or more of the possible causes discussed earlier be the issue here?

Cheat Sheet

Use some commands that could be used for troubleshooting the issue.

This is not a complete list of commands/tools to help troubleshoot this issue. There are various ways/methods/approaches to troubleshoot an issue. These commands/tools referenced here could be used to look up/identify useful information for this lab.

  1. To generate the Windows cluster log by using Windows PowerShell, run the following cmdlet in an elevated PowerShell window:

    PowerShell
    Get-ClusterLog

  2. By default, the log file is created in %WINDIR%\cluster\reports.

    The Cluster.log file entries are in logged in Coordinated Universal Time (UTC).

    To get more information on the error numbers in the cluster log, Net helpmsg could be used (Command prompt).

    CMD Net helpmsg 8557

Congratulations!

You have successfully completed this exercise. Click Next to advance to the next lab.